BatchCollisionCount Property and Update Method Example

This example uses the BatchCollisionCount property and the Update method to demonstrate batch updating where any collisions are resolved by forcing the batch update.

This example assumes the table contains a primary key.

Sub BatchX()

    Dim wrkMain As Workspace
    Dim conMain As Connection
    Dim rstTemp As Recordset
    Dim intLoop As Integer
    Dim strPrompt As String

    Set wrkMain = CreateWorkspace("ODBCWorkspace", _
        "admin", "", dbUseODBC)
    ' This DefaultCursorDriver setting is required for
    ' batch updating.
    wrkMain.DefaultCursorDriver = dbUseClientBatchCursor

    Set conMain = wrkMain.OpenConnection("Publishers", _
        dbDriverNoPrompt, False, _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")
    ' The following locking argument is required for
    ' batch updating.
    Set rstTemp = conMain.OpenRecordset( _
        "SELECT * FROM roysched", dbOpenDynaset, 0, _
        dbOptimisticBatch)

    With rstTemp
        ' Modify data in local recordset.
        Do While Not .EOF
            .Edit
            If !royalty <= 20 Then
                !royalty = !royalty - 4
            Else
                !royalty = !royalty + 2
            End If
            .Update
            .MoveNext
        Loop

        ' Attempt a batch update.
        .Update dbUpdateBatch

        ' If there are collisions, give the user the option
        ' of forcing the changes or resolving them
        ' individually.
        If .BatchCollisionCount > 0 Then
            strPrompt = "There are collisions. " & vbCr & _
                "Do you want the program to force " & _
                vbCr & "an update using the local data?"
            If MsgBox(strPrompt, vbYesNo) = vbYes Then _
                .Update dbUpdateBatch, True
        End If

        .Close
    End With

    conMain.Close
    wrkMain.Close

End Sub